﻿using System;
using System.Configuration;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.IO;
using Telerik.Web.UI;
using System.Data.SqlClient;
using System.Data;

namespace NCC_SOP
{
    public partial class DPMStats : System.Web.UI.Page
    {
        private static string lvl1SelectedValue = "";
        private static string lvl2SelectedValue = "";
        private static string lvl3SelectedValue = "";
        protected void Page_Load(object sender, EventArgs e)
        {
           // if (!Page.IsPostBack)
                // Fill the Data Source
               // LoadRptName();
        }

        protected void RadComboBox1_SelectedIndexChanged(Object sender, RadComboBoxSelectedIndexChangedEventArgs e)
        {
            ResetAllValues();
            lvl1SelectedValue = e.Value;

            // To remove a combo box modify the code below
            RadComboBox2.Visible = true;
            RadComboBox3.Visible = true;

            RebuildAllComboBox(lvl1SelectedValue, lvl2SelectedValue, lvl3SelectedValue);

            if (lvl1SelectedValue == "Death statistics")
            {
                RadComboBox2.Visible = false;
                RadComboBox3.Visible = false;
            }
            else
                if (lvl1SelectedValue == "SHC statistics")
                {
                    RadComboBox2.Visible = false;                    
                }
        }

        protected void RadComboBox2_SelectedIndexChanged(Object sender, RadComboBoxSelectedIndexChangedEventArgs e)
        {
            lvl2SelectedValue = e.Value;
            lvl3SelectedValue = "";
            RebuildAllComboBox(lvl1SelectedValue, lvl2SelectedValue, lvl3SelectedValue);
        }

        protected void RadComboBox3_SelectedIndexChanged(Object sender, RadComboBoxSelectedIndexChangedEventArgs e)
        {
            lvl3SelectedValue = e.Value;
            RebuildAllComboBox(lvl1SelectedValue, lvl2SelectedValue, lvl3SelectedValue);
        }

        // Reset all values
        private void ResetAllValues()
        {
            lvl1SelectedValue = "";
            lvl2SelectedValue = "";
            lvl3SelectedValue = "";
        }

        // Reset combox 2 and 3 when the 1 combox is reselected, so that user re-select, he has to follow the selection sequence.
        private void RebuildCombinations()
        {
            RadComboBox2.SelectedValue = null;
            RadComboBox2.Items.Clear();
            RadComboBox3.SelectedValue = null;
            RadComboBox3.Items.Clear();
            RadComboBox1.BackColor = System.Drawing.Color.White;
            RadComboBox1.ForeColor = System.Drawing.Color.Black;
            RadComboBox2.BackColor = System.Drawing.Color.White;
            RadComboBox2.ForeColor = System.Drawing.Color.Black;
            RadComboBox3.BackColor = System.Drawing.Color.White;
            RadComboBox3.ForeColor = System.Drawing.Color.Black;
        }

        // Rebuild combo box when level 1 or 2 or 3 is selected
        private void RebuildAllComboBox(string level1, string level2, string level3)
        {
            // Rebuild level 2 and 3 when level 1 is selected
            if (level2 == "" && level3 == "")
            {
                // Turns visibility of rad list box
                RadListBox1.Visible = true;

                // Clearing list box and combo boxes
                RadListBox1.Items.Clear();
                RadComboBox2.Text = "";
                RadComboBox3.Text = "";
                RadComboBox2.SelectedValue = null;
                RadComboBox2.Items.Clear();
                RadComboBox3.SelectedValue = null;
                RadComboBox3.Items.Clear();
                RadComboBox2.BackColor = System.Drawing.Color.White;
                RadComboBox2.ForeColor = System.Drawing.Color.Black;
                RadComboBox3.BackColor = System.Drawing.Color.White;
                RadComboBox3.ForeColor = System.Drawing.Color.Black;
                LoadAggregateType(level1);
                LoadAdmissionType2(lvl1SelectedValue);
                RadComboBox1.BackColor = System.Drawing.Color.LightGray;
                RadComboBox1.ForeColor = System.Drawing.Color.Black;
                LoadRptName2(level1);
            }
            // Rebuild level 3 when level 2 is selected
            else if (level2 != "" && level3 == "")
            {
                // Turn visibility of and clear the rad combo box 3
                RadComboBox3.Text = "";
                RadComboBox3.SelectedValue = null;
                RadComboBox3.Items.Clear();
                RadComboBox3.BackColor = System.Drawing.Color.White;
                RadComboBox3.ForeColor = System.Drawing.Color.Black;
                LoadAdmissionType(level2, level1);
                LoadRptName4(level2, level1);
                RadComboBox2.BackColor = System.Drawing.Color.LightGray;
                RadComboBox2.ForeColor = System.Drawing.Color.Black;
            }
            else
            {
                if (level2 == "")
                {
                    LoadRptName3(level3, level1);
                    RadComboBox3.BackColor = System.Drawing.Color.LightGray;
                    RadComboBox3.ForeColor = System.Drawing.Color.Black;
                }
                else
                {
                    LoadRptName(level3, level1, level2);
                    RadComboBox3.BackColor = System.Drawing.Color.LightGray;
                    RadComboBox3.ForeColor = System.Drawing.Color.Black;
                }
            }

        }

        // Load Admission Type (level2, level1)
        private void LoadAdmissionType(string aggregateType, string statsType)
        {

            SqlConnection connection = new SqlConnection(
                ConfigurationManager.ConnectionStrings["localSQLString"].ConnectionString);

            SqlDataAdapter adapter = new SqlDataAdapter("select distinct TableAttributeValue from MetadataDPMTableAttributeValues where TableId in ( select distinct TableId from MetadataDPMTableAttributeValues where TableId in (select MetadataDPMTables.TableId from MetadataDPMTables inner join MetadataDPMTableAttributeValues on MetadataDPMTables.TableId = MetadataDPMTableAttributeValues.TableId where TableAttributeId = 1 and tableAttributeValue = @statsType) and TableAttributeId = 2 and TableAttributeValue=@aggregateType)and TableAttributeId = 3", connection);
            adapter.SelectCommand.Parameters.AddWithValue("@aggregateType", aggregateType);
            adapter.SelectCommand.Parameters.AddWithValue("@statsType", statsType);
            DataTable dt = new DataTable();

            adapter.Fill(dt);

            RadComboBox3.DataTextField = "TableAttributeValue";
            RadComboBox3.DataValueField = "TableAttributeValue";
            RadComboBox3.DataSource = dt;
            RadComboBox3.DataBind();
        }

        // Load Admission Type (level1)
        private void LoadAdmissionType2(string statsType)
        {
            SqlConnection connection = new SqlConnection(
                ConfigurationManager.ConnectionStrings["localSQLString"].ConnectionString);

            SqlDataAdapter adapter = new SqlDataAdapter("select distinct TableAttributeValue from MetadataDPMTableAttributeValues where TableId in ( select distinct TableId from MetadataDPMTableAttributeValues where TableId in (select MetadataDPMTables.TableId from MetadataDPMTables inner join MetadataDPMTableAttributeValues on MetadataDPMTables.TableId = MetadataDPMTableAttributeValues.TableId where TableAttributeId = 1 and tableAttributeValue = @statsType))and TableAttributeId = 3", connection);
            adapter.SelectCommand.Parameters.AddWithValue("@statsType", statsType);
            DataTable dt = new DataTable();

            adapter.Fill(dt);

            RadComboBox3.DataTextField = "TableAttributeValue";
            RadComboBox3.DataValueField = "TableAttributeValue";
            RadComboBox3.DataSource = dt;
            RadComboBox3.DataBind();
        }

        // Load Aggregate Type (level1)
        private void LoadAggregateType(string statsType)
        { 
            SqlConnection connection = new SqlConnection(
                ConfigurationManager.ConnectionStrings["localSQLString"].ConnectionString);
            SqlDataAdapter adapter = new SqlDataAdapter("select distinct TableAttributeValue from MetadataDPMTableAttributeValues where TableId in (select MetadataDPMTables.TableId from MetadataDPMTables inner join MetadataDPMTableAttributeValues on MetadataDPMTables.TableId = MetadataDPMTableAttributeValues.TableId where TableAttributeId = 1 and TableAttributeValue= @statsType) and TableAttributeId = 2", connection);
            adapter.SelectCommand.Parameters.AddWithValue("@statsType", statsType);

            DataTable dt = new DataTable();
            adapter.Fill(dt);

            RadComboBox2.DataTextField = "TableAttributeValue";
            RadComboBox2.DataValueField = "TableAttributeValue";
            RadComboBox2.DataSource = dt;
            RadComboBox2.DataBind();
        }

        // Load Report Name (level3, level1, level2)
        private void LoadRptName(string admissionType, string statsType, string aggregateType)
        {
            SqlConnection connection = new SqlConnection(
            ConfigurationManager.ConnectionStrings["localSQLString"].ConnectionString);

            using (connection)
            {
                DataTable dt = new DataTable();
                using (SqlCommand cmd = new SqlCommand("getLVL2TableID", connection))
                using (var da = new SqlDataAdapter(cmd))
                {
                    cmd.CommandType = CommandType.StoredProcedure;

                    cmd.Parameters.Add("@statsType", SqlDbType.VarChar).Value = statsType;
                    cmd.Parameters.Add("@aggregateType", SqlDbType.VarChar).Value = aggregateType;
                    cmd.Parameters.Add("@admissionType", SqlDbType.VarChar).Value = admissionType;

                    connection.Open();                    
                    cmd.ExecuteNonQuery();

                    
                   
                        da.Fill(dt);

                        RadListBox1.DataTextField = "rptName";
                        RadListBox1.DataValueField = "TableId";
                        RadListBox1.DataSource = dt;
                        RadListBox1.DataBind();
                    
                }
            }
        }

        // Load Report Name (level1)
        private void LoadRptName2(string statsType)
        {
            SqlConnection connection = new SqlConnection(
            ConfigurationManager.ConnectionStrings["localSQLString"].ConnectionString);

            using (connection)
            {
                DataTable dt = new DataTable();
                using (SqlCommand cmd = new SqlCommand("getLVL2TableID2", connection))
                using (var da = new SqlDataAdapter(cmd))
                {
                    cmd.CommandType = CommandType.StoredProcedure;

                    cmd.Parameters.Add("@statsType", SqlDbType.VarChar).Value = statsType;

                    connection.Open();
                    cmd.ExecuteNonQuery();



                    da.Fill(dt);

                    RadListBox1.DataTextField = "rptName";
                    RadListBox1.DataValueField = "TableId";
                    RadListBox1.DataSource = dt;
                    RadListBox1.DataBind();

                }
            }
        }

        // Load Report Name (level3, level1)
        private void LoadRptName3(string admissionType, string statsType)
        {
            SqlConnection connection = new SqlConnection(
            ConfigurationManager.ConnectionStrings["localSQLString"].ConnectionString);

            using (connection)
            {
                DataTable dt = new DataTable();
                using (SqlCommand cmd = new SqlCommand("getLVL2TableID3", connection))
                using (var da = new SqlDataAdapter(cmd))
                {
                    cmd.CommandType = CommandType.StoredProcedure;

                    cmd.Parameters.Add("@statsType", SqlDbType.VarChar).Value = statsType;
                    cmd.Parameters.Add("@admissionType", SqlDbType.VarChar).Value = admissionType;

                    connection.Open();
                    cmd.ExecuteNonQuery();

                    da.Fill(dt);

                    RadListBox1.DataTextField = "rptName";
                    RadListBox1.DataValueField = "TableId";
                    RadListBox1.DataSource = dt;
                    RadListBox1.DataBind();

                }
            }
        }

        // Load Report Name (level2, level1)
        private void LoadRptName4(string aggregateType, string statsType)
        {
            SqlConnection connection = new SqlConnection(
            ConfigurationManager.ConnectionStrings["localSQLString"].ConnectionString);

            using (connection)
            {
                DataTable dt = new DataTable();
                using (SqlCommand cmd = new SqlCommand("getLVL2TableID4", connection))
                using (var da = new SqlDataAdapter(cmd))
                {
                    cmd.CommandType = CommandType.StoredProcedure;

                    cmd.Parameters.Add("@statsType", SqlDbType.VarChar).Value = statsType;
                    cmd.Parameters.Add("@aggregateType", SqlDbType.VarChar).Value = aggregateType;

                    connection.Open();
                    cmd.ExecuteNonQuery();

                    da.Fill(dt);

                    RadListBox1.DataTextField = "rptName";
                    RadListBox1.DataValueField = "TableId";
                    RadListBox1.DataSource = dt;
                    RadListBox1.DataBind();

                }
            }
        }

        // Get Report Name from LoadRptName
        private string getReportName(int TableId)
        {
            String ReportName = "";
            SqlConnection connection = new SqlConnection(
            ConfigurationManager.ConnectionStrings["localSQLString"].ConnectionString);

            using (connection)
            {
                DataTable dt = new DataTable();
                using (SqlCommand cmd = new SqlCommand("select rptName from MetadataDPMTables where TableId=@TableId", connection))
                using (var da = new SqlDataAdapter(cmd))
                {
                    cmd.CommandType = CommandType.Text;

                    cmd.Parameters.Add("@TableId", SqlDbType.Int).Value = TableId;

                    connection.Open();
                    cmd.ExecuteNonQuery();

                    da.Fill(dt);
                    ReportName = "rptName";
                }
            }
            return ReportName;
        }

        protected void Button1_Click(object sender, EventArgs e)
        {
            string tableid = this.RadListBox1.SelectedValue;
            string url = "";
            SqlConnection connection = new SqlConnection(
                ConfigurationManager.ConnectionStrings["localSQLString"].ConnectionString);
            using (connection)
            {
                using (SqlCommand cmd = new SqlCommand("select fileName from MetadataDPMTables where TableId=@TableId", connection))
                {
                    cmd.Parameters.Add("@TableId", SqlDbType.VarChar).Value = tableid;
                    connection.Open();
                    SqlDataReader rdr = cmd.ExecuteReader();
                    if (rdr.HasRows)
                    {
                        rdr.Read();
                        url = rdr.GetString(0).Trim();
                    }
                    rdr.Close();
                }
                Response.Redirect(url);
            }
        }

    }
}